package capaDatos;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import capaDominio.utilsIO;

public class BaseDatos {

	private static Connection conn;
	
	public BaseDatos(){
        try {
    		Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:travelassistant.db");
            Statement stat = conn.createStatement(); 
            try{
            	stat.executeUpdate("drop table users;");
            	stat.executeUpdate("create table users (username, password, status);");
            	insertarUsuario("ferran", "ferranpass", "admin");
                insertarUsuario("tomas", "tomaspass", "worker");
                insertarUsuario("random", "randompass", "worker");
                insertarUsuario("admin", "adminpass", "admin");
            }catch(SQLException e){
            	System.out.println("Tabla users ya existente");
            }

            try{
            	stat.executeUpdate("drop table contactos;");
            	stat.executeUpdate("create table contactos (username, contacto);");
                actualizaContacto("tomas", "ferran");
                actualizaContacto("ferran", "tomas");
            }catch(SQLException e){
            	System.out.println("Tabla contactos ya existente");
            }
            
            try{
            	stat.executeUpdate("drop table itinerarios;");
            	stat.executeUpdate("create table itinerarios (id, user);");
            	insertarItinerario("Viaje Francia","tomas");
            }catch(SQLException e){
            	System.out.println("Tabla itinerarios ya existente");
            }
            
            
            try{
            	stat.executeUpdate("drop table checkpoint;");
            	stat.executeUpdate("create table checkpoint (fecha, id, visitado, foto, posicion, itinerario);");
            	insertarCheckpoint("2012/01/26", "Paris", "no", "", "Paris, Francia", "Viaje Francia");
            	insertarCheckpoint("2012/02/04", "Germany", "no", "", "Bremen, Germany", "Viaje Francia");
            	insertarCheckpoint("2012/05/04", "Italy", "no", "", "Tuscany Italy", "Viaje Francia");

            }catch(SQLException e){
            	System.out.println("Tabla checkpoint ya existente");
            }
            
            try{
            	stat.executeUpdate("drop table viaje;");
            	stat.executeUpdate("create table viaje (origen, destino, medio, hora);");
            	insertarViaje("Paris", "Germany", "tren", "14:00");
            	insertarViaje("Germany", "Italy", "avion", "03:00");
            }catch(SQLException e){
            	System.out.println("Tabla viaje ya existente");
            }
            
            try{
            	stat.executeUpdate("drop table productos;");
            	stat.executeUpdate("create table productos (id, precio, rating, cantidad);");
            	insertarProducto("chair","20","3","8");
            	insertarProducto("paper","0.02","4","520");
            }catch(SQLException e){
            	System.out.println("Tabla productos ya existente");
            }
            
            /*try{
            	stat.executeUpdate("create table ordenes (id);");
            }catch(SQLException e){
            	System.out.println("Tabla ordenes ya existente");
            }
            */
            try{
            	stat.executeUpdate("drop table mapeado;");
            	stat.executeUpdate("create table mapeado (producto, orden, cantidad);");
            }catch(SQLException e){
            	System.out.println("Tabla mapeado ya existente");
            }

            //Volcado de la BD
            printUsers();
            printContactos();
             
             
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        } catch (ClassNotFoundException ex) {
            System.out.println(ex.getMessage());
        }
	}
	
	public static void insertarUsuario(String uname, String upass, String status) {
    	PreparedStatement prep;
		try {
			prep = conn.prepareStatement("INSERT INTO users (username,password,status) VALUES (?, ?, ?);");
			prep.setString(1, uname);
	        prep.setString(2, utilsIO.getHash(upass));
	        prep.setString(3, status);
	        prep.addBatch();
	        prep.executeBatch();
	        //conn.commit();
	        prep.close();
	        System.out.println("Nuevo usuario (tipo: "+status+") registrado: " + uname);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void insertarContacto(String uname, String contacto) {
    	PreparedStatement prep;
		try {
			prep = conn.prepareStatement("INSERT INTO contactos (username, contacto) VALUES (?, ?);");
			prep.setString(1, uname);
	        prep.setString(2, contacto);
	        prep.addBatch();
	        prep.executeBatch();
	        //conn.commit();
	        prep.close();
	        System.out.println("Nuevo contacto ("+contacto+") para el usuario: " + uname);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void insertarItinerario(String id, String user) {
    	PreparedStatement prep;
		try {
			prep = conn.prepareStatement("INSERT INTO itinerarios (id, user) VALUES (?, ?);");
			prep.setString(1, id);
	        prep.setString(2, user);
	        prep.addBatch();
	        prep.executeBatch();
	        //conn.commit();
	        prep.close();
	        System.out.println("Nuevo itinerario ("+id+") para el usuario: " + user);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	//create table checkpoint (fecha, id, visitado, foto, posicion, itinerario)
	public static void insertarCheckpoint(String fecha, String id, String visitado, String foto, String posicion, String itinerario) {
    	PreparedStatement prep;
		try {
			prep = conn.prepareStatement("INSERT INTO checkpoint (fecha, id, visitado, foto, posicion, itinerario) VALUES (?, ?, ?, ?, ?, ?);");
			prep.setString(1, fecha);
	        prep.setString(2, id);
	        prep.setString(3, visitado);
	        prep.setString(4, foto);
	        prep.setString(5, posicion);
	        prep.setString(6, itinerario);
	        prep.addBatch();
	        prep.executeBatch();
	        //conn.commit();
	        prep.close();
	        System.out.println("Nuevo checkpoint ("+fecha+", "+id+", "+visitado+", "+foto+", "+posicion+", "+itinerario+")");;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	//viaje (origen, destino, medio, hora)
	public static void insertarViaje(String origen, String destino, String medio, String hora) {
    	PreparedStatement prep;
		try {
			prep = conn.prepareStatement("INSERT INTO viaje (origen, destino, medio, hora) VALUES (?, ?, ?, ?);");
			prep.setString(1, origen);
	        prep.setString(2, destino);
	        prep.setString(3, medio);
	        prep.setString(4, hora);
	        prep.addBatch();
	        prep.executeBatch();
	        //conn.commit();
	        prep.close();
	        System.out.println("Nuevo viaje de "+origen+" a "+destino+" en "+medio+" a las "+hora);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	//productos (id, precio, rating, cantidad)
	public static void insertarProducto(String id, String precio, String rating, String cantidad) {
    	PreparedStatement prep;
		try {
			prep = conn.prepareStatement("INSERT INTO productos (id, precio, rating, cantidad) VALUES (?, ?, ?, ?);");
			prep.setString(1, id);
	        prep.setString(2, precio);
	        prep.setString(3, rating);
	        prep.setString(4, cantidad);
	        prep.addBatch();
	        prep.executeBatch();
	        //conn.commit();
	        prep.close();
	        System.out.println("Nuevo producto "+id+" a "+precio+" valorado en "+rating+". Disponibilidad: "+cantidad);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static void printUsers(){
		try{
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select * from users;");
	        System.out.println("-----------------------------------");
	    	System.out.println("-------------USUARIOS--------------");
	        while (rs.next()) {
	            System.out.println("username...: " + rs.getString("username"));
	            System.out.println("password...: " + rs.getString("password"));
	            System.out.println("status...: " + rs.getString("status"));
	            System.out.println("-----------------------------------");
	        }
	        rs.close();
	        stat.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void printContactos(){
		try{
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select * from contactos;");
	        System.out.println("-----------------------------------");
	    	System.out.println("-------------FAVORITOS--------------");
	        while (rs.next()) {
	            System.out.println("username...: " + rs.getString("username") + ", contacto...: " + rs.getString("contacto"));
	            System.out.println("-----------------------------------");
	        }
	        rs.close();
	        stat.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static ResultSet getUser(String usuario, String password) {
		try {
			Statement stat = conn.createStatement();
			return stat.executeQuery("select * from users where username='" + usuario + "' and password='" + utilsIO.getHash(password)+"';");

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static ResultSet getUser(String usuario) {
		try {
			Statement stat = conn.createStatement();
			return stat.executeQuery("select * from users where username='" + usuario + "';");

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static boolean actualizaContacto(String uname, String con){
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select * from contactos where username='" + uname + "' and contacto='"+con+"';");
			if(rs.next()){
				System.out.println("BaseDatos.actualizaContacto: Query= delete from contactos where username='" + uname + "' and contacto='"+con+"';");
				stat.executeUpdate("delete from contactos where username='" + uname + "' and contacto='"+con+"';");
				rs.close();
				
				rs = stat.executeQuery("select * from contactos where username='" + con + "' and contacto='"+uname+"';");
				if(rs.next()){
					System.out.println("BaseDatos.actualizaContacto: Query= delete from contactos where username='" + con + "' and contacto='"+uname+"';");
					stat.executeUpdate("delete from contactos where username='" + con + "' and contacto='"+uname+"';");
				}
			}
			else{
				System.out.println("BaseDatos.actualizaContacto: Query insert into contactos (username, contacto) VALUES (?,?);");
				PreparedStatement prep2 = conn.prepareStatement("insert into contactos (username, contacto) VALUES (?,?);");
				prep2.setString(1, uname);
				prep2.setString(2, con);
		        prep2.addBatch();
	            prep2.executeBatch();
				prep2.close();
			}
			rs.close();
			stat.close();
			printContactos();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public static ResultSet consultarContacto(String uname){
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select * from contactos where username='" + uname+"';");
			return rs;
		}catch(SQLException e){
			e.printStackTrace();
		}
		return null;
	}
	
	public static ResultSet consultarItinerario(String user){
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select * from itinerarios where user='"+user+"';");
			return rs;
		}catch(SQLException e){
			e.printStackTrace();
		}
		return null;
	}
	

	public static ResultSet consultarCheckpoint(String id){
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select * from checkpoint where id='" + id+"';");
			return rs;
		}catch(SQLException e){
			e.printStackTrace();
		}
		return null;
	}
	
	public static ResultSet actualizaImagenCheckpoint(String id, String foto){
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("update checkpoint set foto='" +foto+"' visitado='si' where id='"+id+"';");
			return rs;
		}catch(SQLException e){
			e.printStackTrace();
		}
		return null;
	}
	
	public static ResultSet consultarViaje(String origen, String destino){
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select * from viaje where origen='" +origen+"' AND destino='"+destino+"';");
			return rs;
		}catch(SQLException e){
			e.printStackTrace();
		}
		return null;
	}
	
	public static ResultSet consultarProducto(String id){
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select * from productos where id='" + id+"';");
			return rs;
		}catch(SQLException e){
			e.printStackTrace();
		}
		return null;
	}
	
	public static ResultSet listarProductos(){
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select * from productos;");
			return rs;
		}catch(SQLException e){
			e.printStackTrace();
		}
		return null;
	}
	
	public static ResultSet listarCheckpoints(String id){
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select * from checkpoint where itinerario='"+id+"';");
			return rs;
		}catch(SQLException e){
			e.printStackTrace();
		}
		return null;
	}
	
	public static ResultSet listarCheckpointsPorFecha(String id){
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select * from checkpoint where itinerario='"+id+"' order by fecha;");
			return rs;
		}catch(SQLException e){
			e.printStackTrace();
		}
		return null;
	}
	
	public static ResultSet consultarMapeado(String producto, String orden){
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select * from mapeado where producto='" +producto+"' AND orden='"+orden+"';");
			return rs;
		}catch(SQLException e){
			e.printStackTrace();
		}
		return null;
	}

	public static ResultSet getStatusUser(String usuario) {
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select status from users where username='" + usuario+"';");
			return rs;
		}catch(SQLException e){
			e.printStackTrace();
		}
		return null;
	}
}
